/***********************************************************************************************************************************
 *    Name: DBAdmin_CMS.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: DBAdmin Toolkit Central Management Server
**/
USE DBAdmin;
GO

-- SQL Builds ======================================================================================================================
IF OBJECT_ID(N'dbo.SQLBuilds') IS NOT Null DROP TABLE dbo.SQLBuilds;
CREATE TABLE dbo.SQLBuilds (
    ProductVersion	NVARCHAR(16)	NOT Null PRIMARY KEY CLUSTERED,
    Major			AS CAST(PARSENAME(CONVERT(VARCHAR(32), ProductVersion), 4) AS TINYINT),
    Minor			AS CAST(PARSENAME(CONVERT(VARCHAR(32), ProductVersion), 3) AS TINYINT),
    Build			AS CAST(PARSENAME(CONVERT(varchar(32), ProductVersion), 2) AS SMALLINT),
    Revision		AS CAST(PARSENAME(CONVERT(VARCHAR(32), ProductVersion), 1) AS TINYINT),
    CommonVersion	AS CAST(PARSENAME(CONVERT(VARCHAR(32), ProductVersion), 4) + '.' + PARSENAME(CONVERT(VARCHAR(32), ProductVersion), 3) AS FLOAT),
	Description		NVARCHAR(64)	NOT Null UNIQUE,
	IsLatest		BIT				NOT Null,
	IsTarget		BIT				NOT Null);
TRUNCATE TABLE dbo.SQLBuilds;
INSERT INTO dbo.SQLBuilds

	(ProductVersion		, Description								, IsLatest	, IsTarget	) VALUES
	(N'13.0.400.361'	, N'SQL Server 2016 RC3'					, 1			, 0			),
	(N'13.0.1300.275'	, N'SQL Server 2016 RC2'					, 0			, 0			),
	(N'13.0.1200.242'	, N'SQL Server 2016 RC1'					, 0			, 0			),

--	 ProductVersion		, Description								, IsLatest	, IsTarget
	(N'12.0.4449.1'		, N'SQL Server 2014 SP1 CU6'				, 1			, 0			),
	(N'12.0.4439.1'		, N'SQL Server 2014 SP1 CU5'				, 0			, 0			),
	(N'12.0.4436.0'		, N'SQL Server 2014 SP1 CU4'				, 0			, 0			),
	(N'12.0.4427.24'	, N'SQL Server 2014 SP1 CU3'				, 0			, 0			),
	(N'12.0.4422.0'		, N'SQL Server 2014 SP1 CU2'				, 0			, 0			),
	(N'12.0.4416.0'		, N'SQL Server 2014 SP1 CU1'				, 0			, 0			),
	(N'12.0.4100.1'		, N'SQL Server 2014 SP1'					, 0			, 1			),
	(N'12.0.2564.0'		, N'SQL Server 2014 CU12'					, 0			, 0			),
	(N'12.0.2560.0'		, N'SQL Server 2014 CU11'					, 0			, 0			),
	(N'12.0.2556.4'		, N'SQL Server 2014 CU10'					, 0			, 0			),
	(N'12.0.2553.0'		, N'SQL Server 2014 CU9'					, 0			, 0			),
	(N'12.0.2546.0'		, N'SQL Server 2014 CU8'					, 0			, 0			),
	(N'12.0.2495.0'		, N'SQL Server 2014 CU7'					, 0			, 0			),
	(N'12.0.2480.0'		, N'SQL Server 2014 CU6'					, 0			, 0			),
	(N'12.0.2456.0'		, N'SQL Server 2014 CU5'					, 0			, 0			),
	(N'12.0.2430.0'		, N'SQL Server 2014 CU4'					, 0			, 0			),
	(N'12.0.2402.0'		, N'SQL Server 2014 CU3'					, 0			, 0			),
	(N'12.0.2370.0'		, N'SQL Server 2014 CU2'					, 0			, 0			),
	(N'12.0.2342.0'		, N'SQL Server 2014 CU1'					, 0			, 0			),
	(N'12.0.2000.8'		, N'SQL Server 2014 RTM'					, 0			, 0			),

--	 ProductVersion		, Description								, IsLatest	, IsTarget
	(N'11.0.6523.0'		, N'SQL Server 2012 SP3 CU2'				, 1			, 0			),
	(N'11.0.6518.0'		, N'SQL Server 2012 SP3 CU1'				, 0			, 0			),
	(N'11.0.6020.0'		, N'SQL Server 2012 SP3'					, 0			, 1			),
	(N'11.0.5644.1'		, N'SQL Server 2012 SP2 CU10'				, 0			, 0			),
	(N'11.0.5641.1'		, N'SQL Server 2012 SP2 CU9'				, 0			, 0			),
	(N'11.0.5634.1'		, N'SQL Server 2012 SP2 CU8'				, 0			, 0			),
	(N'11.0.5623.0'		, N'SQL Server 2012 SP2 CU7'				, 0			, 0			),
	(N'11.0.5592.0'		, N'SQL Server 2012 SP2 CU6'				, 0			, 0			),
	(N'11.0.5582.0'		, N'SQL Server 2012 SP2 CU5'				, 0			, 0			),
	(N'11.0.5569.0'		, N'SQL Server 2012 SP2 CU4'				, 0			, 0			),
	(N'11.0.5556.0'		, N'SQL Server 2012 SP2 CU3'				, 0			, 0			),
	(N'11.0.5548.0'		, N'SQL Server 2012 SP2 CU2'				, 0			, 0			),
	(N'11.0.5532.0'		, N'SQL Server 2012 SP2 CU1'				, 0			, 1			),
	(N'11.0.5058.0'		, N'SQL Server 2012 SP2 - DANGER!'			, 0			, 0			),
	(N'11.0.2100.60'	, N'SQL Server 2012 RTM'					, 0			, 0			),

--	 ProductVersion		, Description								, IsLatest	, IsTarget
--	(N'10.50.6525.0'	, N'SQL Server 2008 R2 SP3 + KB 3033860'	, 0			, 0			),
	(N'10.50.6000.34'	, N'SQL Server 2008 R2 SP3'					, 1			, 1			),
	(N'10.50.4033.0'	, N'SQL Server 2008 R2 SP2 + MS14-044'		, 0			, 0			),
	(N'10.50.4000.0'	, N'SQL Server 2008 R2 SP2'					, 0			, 0			),
	(N'10.50.2861.0'	, N'SQL Server 2008 R2 SP1 CU11 + MS12-070'	, 0			, 0			),
	(N'10.50.2550.0'	, N'SQL Server 2008 R2 SP1 + MS12-070'		, 0			, 0			),
	(N'10.50.2500.0'	, N'SQL Server 2008 R2 SP1'					, 0			, 0			),
	(N'10.50.1617.0'	, N'SQL Server 2008 R2 MS11-049'			, 0			, 0			),
	(N'10.50.1600.1'	, N'SQL Server 2008 R2 RTM'					, 0			, 0			),

--	 ProductVersion		, Description								, IsLatest	, IsTarget
	(N'10.0.6000.29'	, N'SQL Server 2008 SP4'					, 1			, 1			),
	(N'10.0.5829.0'		, N'SQL Server 2008 SP3 CU9'				, 0			, 0			),
	(N'10.0.2766.0'		, N'SQL Server 2008 SP1 CU7'				, 0			, 0			),
	(N'10.0.1600.22'	, N'SQL Server 2008 RTM'					, 0			, 0			),

--	 ProductVersion		, Description								, IsLatest	, IsTarget
	(N'9.00.5324.00'	, N'SQL Server 2005 SP4 + CU4 + MS12-070'	, 1			, 1			),
	(N'9.00.5069.00'	, N'SQL Server 2005 SP4 + MS12-070'			, 0			, 0			),
	(N'9.00.5000.00'	, N'SQL Server 2005 SP4'					, 0			, 1			),
	(N'9.00.4035.00'	, N'SQL Server 2005 SP3'					, 0			, 0			),
	(N'9.00.3042.00'	, N'SQL Server 2005 SP2'					, 0			, 0			),
	(N'9.00.2047.00'	, N'SQL Server 2005 SP1'					, 0			, 0			),
	(N'9.00.1399.06'	, N'SQL Server 2005 RTM'					, 0			, 0			),

--	 ProductVersion		, Description								, IsLatest	, IsTarget
	(N'8.00.2039.0'		, N'SQL Server 2000 SP4'					, 1			, 1			),
	(N'8.00.760.0'		, N'SQL Server 2000 SP3(a)'					, 0			, 0			);
GO


-- Memory Configuration Tally Table=================================================================================================
-- http://dwaincsql.com/2014/03/27/tally-tables-in-t-sql/
IF OBJECT_ID(N'dbo.SQL_MemoryConfig') IS Null EXECUTE (N'CREATE VIEW dbo.SQL_MemoryConfig AS SELECT 0 AS Zero;');
GO
ALTER VIEW dbo.SQL_MemoryConfig AS WITH m (Physical) AS (SELECT 1024*ROW_NUMBER() OVER (ORDER BY (SELECT Null))
  FROM         (VALUES(0),(0),(0),(0),(0),(0),(0),(0)) a(n)		--   8 rows
	CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0)) b(n)		--  64 rows
	CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0)) c(n)	)	-- 512 rows
SELECT
	Physical,
	Instance= CASE WHEN Physical < 4096 THEN Physical-1024 WHEN Physical < 16384 THEN Physical-(1024+((Physical-4096)/4)) ELSE Physical-(4096+((Physical-16384)/8)) END
  FROM m;
GO


-- Server List =====================================================================================================================
IF OBJECT_ID(N'dbo.ServerList') IS Null EXECUTE (N'CREATE VIEW dbo.ServerList AS SELECT 0 AS Zero;');
GO
ALTER VIEW dbo.ServerList AS
WITH h (server_group_id, parent_id, name) AS (
	SELECT g.server_group_id, g.parent_id, g.name FROM msdb.dbo.sysmanagement_shared_server_groups g WHERE g.parent_id = 1 UNION ALL
	SELECT g.server_group_id, g.parent_id, CAST(h.name + N' -> ' + g.name AS SYSNAME) FROM msdb.dbo.sysmanagement_shared_server_groups g INNER JOIN h ON (g.parent_id = h.server_group_id)	)
SELECT Path= h.name, Label= s.name, ServerInstance= s.server_name, ComputerName= SUBSTRING(s.server_name, 1, LEN(s.server_name)-CHARINDEX(N'\',REVERSE(s.server_name)))
  FROM h INNER JOIN msdb.dbo.sysmanagement_shared_registered_servers s ON (s.server_group_id = h.server_group_id)
  WHERE s.server_type = 0;
GO


-- Server Configuration ============================================================================================================
IF OBJECT_ID(N'dbo.OS_Host') IS NOT Null BEGIN
  DROP TABLE dbo.SQL_Server;
  DROP TABLE dbo.OS_Host;
END;
CREATE TABLE dbo.OS_Host (
	ComputerName			NVARCHAR(128)	NOT Null PRIMARY KEY CLUSTERED,
	Manufacturer			NVARCHAR(128)		Null,
	Model					NVARCHAR(128)		Null,
	NumberOfSockets			TINYINT				Null,
	NumberOfCores			TINYINT				Null,
	NumberOfLogicalCores	TINYINT				Null,
	TotalPhysicalMemory		INT					Null,
	IsClustered				BIT				NOT Null DEFAULT 0,
	LogDate					DATETIME2		NOT Null DEFAULT SYSDATETIME());
GO


IF OBJECT_ID(N'dbo.SQL_Server') IS NOT Null DROP TABLE dbo.SQL_Server;
CREATE TABLE dbo.SQL_Server (
	ServerInstance		NVARCHAR(128)	NOT Null PRIMARY KEY CLUSTERED,
	ComputerName		NVARCHAR(128)	NOT Null FOREIGN KEY REFERENCES dbo.OS_Host,
	IPAddress			NVARCHAR( 64)	NOT	Null,
	IsClustered			BIT				NOT NULL DEFAULT 0,
	ProductVersion		NVARCHAR( 16)		Null,
	Edition				NVARCHAR( 32)		Null,
	MaxMemory			INTEGER				Null,
	DefaultData			NVARCHAR(256)		Null,
	DefaultLog			NVARCHAR(256)		Null,
	BackupDirectory		NVARCHAR(256)		Null,
	SQLDataRoot			NVARCHAR(256)		Null,
	ErrorDumpDir		NVARCHAR(256)		Null,
	WorkingDirectory	NVARCHAR(256)		Null,
	LogDate				DATETIME2		NOT Null DEFAULT SYSDATETIME());
GO


IF OBJECT_ID(N'dbo.Log_OSHost') IS Null EXECUTE( N'CREATE PROCEDURE dbo.Log_OSHost AS RETURN 0;');
GO
ALTER PROCEDURE dbo.Log_OSHost (
	@ComputerName			NVARCHAR(128),
	@Manufacturer			NVARCHAR(128)	= NULL,
	@Model					NVARCHAR(128)	= NULL,
	@NumberOfSockets		TINYINT			= NULL,
	@NumberOfCores			TINYINT			= NULL,
	@NumberOfLogicalCores	TINYINT			= NULL,
	@TotalPhysicalMemory	INT				= NULL,
	@IsClustered			BIT				= 0) AS
MERGE dbo.OS_Host AS t
  USING (SELECT @ComputerName) AS s (ComputerName) ON (s.ComputerName = t.ComputerName)
  WHEN MATCHED THEN UPDATE SET
	Manufacturer		= @Manufacturer,
	Model				= @Model,
	NumberOfSockets		= @NumberOfSockets,
	NumberOfCores		= @NumberOfCores,
	NumberOfLogicalCores= @NumberOfLogicalCores,
	TotalPhysicalMemory	= @TotalPhysicalMemory,
	IsClustered			= @IsClustered,
	LogDate				= SYSDATETIME()
  WHEN NOT MATCHED THEN    
	INSERT ( ComputerName,  Manufacturer,  Model,  NumberOfSockets,  NumberOfCores,  NumberOfLogicalCores,  TotalPhysicalMemory,  IsClustered)
	VALUES (@ComputerName, @Manufacturer, @Model, @NumberOfSockets, @NumberOfCores, @NumberOfLogicalCores, @TotalPhysicalMemory, @IsClustered);
GO


IF OBJECT_ID(N'dbo.Log_SQLServer') IS Null EXECUTE( N'CREATE PROCEDURE dbo.Log_SQLServer AS RETURN 0;');
GO
ALTER PROCEDURE dbo.Log_SQLServer (
	@ServerInstance		NVARCHAR(128),
	@ComputerName		NVARCHAR(128),
	@IPAddress			NVARCHAR( 64),
	@IsClustered		BIT				= 0,
	@ProductVersion		NVARCHAR( 16)	= Null,
	@Edition			NVARCHAR( 32)	= Null,
	@MaxMemory			INT				= Null,
	@DefaultData		NVARCHAR(255)	= Null,
	@DefaultLog			NVARCHAR(255)	= Null,
	@BackupDirectory	NVARCHAR(255)	= Null,
	@SQLDataRoot		NVARCHAR(255)	= Null,
	@ErrorDumpDir		NVARCHAR(255)	= Null,
	@WorkingDirectory	NVARCHAR(255)	= Null	) AS

IF NOT EXISTS (SELECT ComputerName FROM dbo.OS_Host WHERE ComputerName = @ComputerName) INSERT INTO dbo.OS_Host (ComputerName) VALUES (@ComputerName);
MERGE dbo.SQL_Server AS t
  USING (SELECT @ServerInstance) AS s (ServerInstance) ON (s.ServerInstance = t.ServerInstance)
  WHEN MATCHED THEN
	UPDATE SET
		ComputerName	= @ComputerName,
		IsClustered		= @IsClustered,
		IPAddress		= ISNull(@IPAddress			, t.IPAddress),
		ProductVersion	= ISNull(@ProductVersion	, t.ProductVersion),
		Edition			= ISNull(@Edition			, t.Edition),
		MaxMemory		= ISNull(@MaxMemory			, t.MaxMemory),
		DefaultData		= ISNull(@DefaultData		, t.DefaultData),
		DefaultLog		= ISNull(@DefaultLog		, t.DefaultLog),
		BackupDirectory	= ISNull(@BackupDirectory	, t.BackupDirectory),
		SQLDataRoot		= ISNull(@SQLDataRoot		, t.SQLDataRoot),
		ErrorDumpDir	= ISNull(@ErrorDumpDir		, t.ErrorDumpDir),
		WorkingDirectory= ISNull(@WorkingDirectory	, t.WorkingDirectory),
		LogDate			= SYSDATETIME()
  WHEN NOT MATCHED THEN    
	INSERT ( ServerInstance,  ComputerName,  IPAddress,  IsClustered,  ProductVersion,  Edition,  MaxMemory,  DefaultData,  DefaultLog,  BackupDirectory,  SQLDataRoot,  ErrorDumpDir,  WorkingDirectory)
	VALUES (@ServerInstance, @ComputerName, @IPAddress, @IsClustered, @ProductVersion, @Edition, @MaxMemory, @DefaultData, @DefaultLog, @BackupDirectory, @SQLDataRoot, @ErrorDumpDir, @WorkingDirectory);
GO


IF OBJECT_ID(N'dbo.ShowConfiguration') IS Null EXECUTE (N'CREATE VIEW dbo.ShowConfiguration AS SELECT 0 AS Zero;');
GO
ALTER VIEW dbo.ShowConfiguration AS SELECT
	sl.Path, sl.Label,
	os.NumberOfSockets, os.NumberOfCores, os.TotalPhysicalMemory,
	ss.ProductVersion, ss.Edition, ss.MaxMemory, ss.DefaultData, ss.DefaultLog, ss.BackupDirectory, ss.SQLDataRoot, ss.ErrorDumpDir, ss.WorkingDirectory
  FROM dbo.ServerList sl
	LEFT JOIN dbo.OS_Host os ON (sl.ComputerName = os.ComputerName)
	LEFT JOIN dbo.SQL_Server ss ON (sl.ServerInstance = ss.ServerInstance);
GO


--==================================================================================================================================
-- SSL Certificate =================================================================================================================

IF OBJECT_ID(N'dbo.SSLCertificate') IS NOT Null DROP TABLE dbo.SSLCertificate;
CREATE TABLE dbo.SSLCertificate (
	ServerInstance	NVARCHAR(128)	NOT Null,
	Thumbprint		NCHAR(40)		NOT Null,
	SerialNumber	NVARCHAR(128)		Null,
	Subject			NVARCHAR(128)		Null,
	Issuer			NVARCHAR(128)		Null,
	NotBefore		DATETIME2(0)		Null,
	NotAfter		DATETIME2(0)		Null,
	UpdateDate		DATETIME2		NOT Null CONSTRAINT [DF-SSLCertificate-UpdateDate]  DEFAULT (SYSDATETIME()),
 CONSTRAINT PK_Certificate PRIMARY KEY CLUSTERED (ServerInstance ASC, Thumbprint ASC));
GO


IF OBJECT_ID(N'dbo.LogCertificate') IS Null EXECUTE( N'CREATE PROCEDURE dbo.LogCertificate AS RETURN 0;');
GO
ALTER PROCEDURE dbo.LogCertificate	(
	@ServerInstance	NVARCHAR(128),
	@Thumbprint		NCHAR(40),
	@SerialNumber	NVARCHAR(128)= Null,
	@Subject		NVARCHAR(128)= Null,
	@Issuer			NVARCHAR(128)= Null,
	@NotBefore		DATETIME2(0) = Null,
	@NotAfter		DATETIME2(0) = Null	) AS
IF @SerialNumber = N'' SET @SerialNumber= Null;
IF @Subject      = N'' SET @Subject     = Null;
MERGE dbo.SSLCertificate AS t
  USING (SELECT @ServerInstance, @Thumbprint) AS s (ServerInstance, Thumbprint) ON (s.ServerInstance = t.ServerInstance AND s.Thumbprint = t.Thumbprint)
  WHEN MATCHED THEN 
	UPDATE SET UpdateDate= SYSDATETIME()
  WHEN NOT MATCHED THEN    
	INSERT (ServerInstance, Thumbprint, SerialNumber, Subject, Issuer, NotBefore, NotAfter) VALUES (@ServerInstance, @Thumbprint, @SerialNumber, @Subject, @Issuer, @NotBefore, @NotAfter);
GO


IF OBJECT_ID(N'dbo.ShowCertificateExpiration') IS Null EXECUTE (N'CREATE VIEW dbo.ShowCertificateExpiration AS SELECT 0 AS Zero;');
GO
ALTER VIEW dbo.ShowCertificateExpiration AS
  WITH xc AS (SELECT ServerInstance, Subject, Issuer, NotAfter, UpdateDate, RANK() OVER (PARTITION BY ServerInstance ORDER BY UpdateDate DESC) AS RankOrder FROM dbo.SSLCertificate)
  SELECT sl.*, xc.Subject, xc.Issuer, xc.NotAfter, DATEDIFF(dd,SYSDATETIME(),xc.NotAfter) AS ExpireDays
	FROM dbo.ServerList sl INNER JOIN xc ON (sl.ServerInstance = xc.ServerInstance)
	WHERE xc.RankOrder = 1;
GO


--==================================================================================================================================
-- Replication =====================================================================================================================
IF EXISTS(SELECT * FROM sys.servers WHERE name = N'Distributor') EXECUTE dbo.sp_dropserver N'Distributor';
EXECUTE dbo.sp_addlinkedserver
	@server		= N'Distributor',
	@srvproduct	= N'MSSQLServer',
	@provider	= N'SQLNCLI11',
	@datasrc	= N'BBA-Orl-SQLDist.BBAAviation.net',
	@catalog	= N'DistributionDB';
EXECUTE dbo.sp_addlinkedsrvlogin
	@rmtsrvname	= N'Distributor',
	@useself	= TRUE;
EXECUTE dbo.sp_serveroption @server= N'Distributor', @optname= N'collation compatible',	@optvalue= TRUE;
EXECUTE dbo.sp_serveroption @server= N'Distributor', @optname= N'rpc',					@optvalue= TRUE;
EXECUTE dbo.sp_serveroption @server= N'Distributor', @optname= N'rpc out',				@optvalue= TRUE;
GO


IF OBJECT_ID(N'dbo.SubscriptionCounts') IS Null EXECUTE (N'CREATE VIEW dbo.SubscriptionCounts AS SELECT 0 AS Zero;');
GO
ALTER VIEW dbo.SubscriptionCounts AS
WITH s (Subscriber, Subscriptions) AS (SELECT subscriber, COUNT(DISTINCT publication) FROM Distributor.DistributionDB.DBAdmin.Subscriptions GROUP BY subscriber)
SELECT sl.Path, Subscriber= sl.label, Subscriptions= ISNull(s.Subscriptions,0)
  FROM dbo.ServerList sl LEFT JOIN s ON (sl.ServerInstance = s.Subscriber)
  WHERE Label NOT LIKE N'%(_)';
GO

-- Job Step
SET NOCOUNT ON;
SELECT *
  INTO ##SubscriptionCounts
  FROM DBAdmin.dbo.SubscriptionCounts
  WHERE Path LIKE N'% » % FBOs' AND ISNull(Subscriptions,0) <> 3 AND Subscriber NOT LIKE N'% (-)';
IF 0 < @@ROWCOUNT
  EXECUTE msdb.dbo.sp_send_dbmail
	@recipients				= N'DBANotifications@US.BBAAviation.com',
	@subject				= N'Watchdog Alert: Missing Subscriptions',
	@importance				= N'High',
	@body					= N'Missing Subscriptions:',
	@query					= N'SET NOCOUNT ON; SELECT * FROM ##SubscriptionCounts ORDER BY 1,2;',
	@query_result_header	= 0,
	@query_result_no_padding= 1,
	@query_result_separator	= '	';
DROP TABLE ##SubscriptionCounts;
SET NOCOUNT OFF;
GO


--==================================================================================================================================

-- Inventory =======================================================================================================================
IF OBJECT_ID(N'dbo.Inventory') IS NOT Null DROP TABLE dbo.Inventory;
CREATE TABLE dbo.Inventory (
	Inventory_ID	INT				NOT Null IDENTITY(1,1) CONSTRAINT UQ_Inventory_ID UNIQUE,
	ServerName		NVARCHAR(128)	NOT Null,
	DatabaseName	NVARCHAR(128)	NOT Null,
	Compatibility	TINYINT				NULL,
	State			NVARCHAR(60)		Null,
	Recovery		NVARCHAR(60)		Null,
	Page_Verify		NVARCHAR(60)		Null,
	FullText		BIT					Null,
	Published		BIT					Null,
	Subscribed		BIT					Null,
	Merge_Published	BIT					Null,
	Distributor		BIT					Null,
	UpdateDate		DATETIME2		NOT Null CONSTRAINT [DF-Inventory-UpdateDate] DEFAULT (SYSDATETIME())
	CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (ServerName,DatabaseName));
GO

IF OBJECT_ID(N'dbo.Log_Inventory') IS Null EXECUTE( N'CREATE PROCEDURE dbo.Log_Inventory AS RETURN 0;');
GO
ALTER PROCEDURE dbo.Log_Inventory (
	@ServerName			NVARCHAR(128)	=Null,
	@DatabaseName		NVARCHAR(128)	=Null,
	@Compatibility		TINYINT			=Null,
	@State				NVARCHAR(60)	=Null,
	@Recovery			NVARCHAR(60)	=Null,
	@Page_Verify		NVARCHAR(60)	=Null,
	@FullText			BIT				=Null,
	@Published			BIT				=Null,
	@Subscribed			BIT				=Null,
	@Merge_Published	BIT				=Null,
	@Distributor		BIT				=Null
) AS
MERGE dbo.Inventory AS t
  USING (SELECT @ServerName, @DatabaseName) AS s (ServerName, DatabaseName) ON (s.ServerName = t.ServerName AND s.DatabaseName = t.DatabaseName)
  WHEN MATCHED THEN UPDATE SET
	Compatibility	= @Compatibility,
	State			= @State,
	Recovery		= @Recovery,
	Page_Verify		= @Page_Verify,
	FullText		= @FullText,
	Published		= @Published,
	Subscribed		= @Subscribed,
	Merge_Published	= @Merge_Published,
	Distributor		= @Distributor,
	UpdateDate		= SYSDATETIME()
  WHEN NOT MATCHED THEN
	INSERT ( ServerName,  DatabaseName,  Compatibility,  State,  Recovery,  Page_Verify,  FullText,  Published,  Subscribed,  Merge_Published,  Distributor)
	VALUES (@ServerName, @DatabaseName, @Compatibility, @State, @Recovery, @Page_Verify, @FullText, @Published, @Subscribed, @Merge_Published, @Distributor);
GO